using System.Data;
using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DBAccessLibrary.DBAccessLibrary;
using Chaotic.DataBase.DBAccessLibrary.Interface;
using Chaotic.DataBase.DBAccessLibrary.Model;

namespace Chaotic.DataBase.DBAccessLibrary.Grammar;

internal class SqlGrammar : DBGrammar, IGrammar, ILocalGrammar
{
	public string GetChTableSql
	{
		get
		{
			if (string.IsNullOrEmpty(_xml.ChDescription))
			{
				return "SELECT TABLENAME,CHINESETABLENAME as CHINATABLENAME,FIELDNAME as COLUMNNAME,CHINESENAME as CHINACOLUMNNAME FROM T_FIELD_DICTIONARY order by TABLENAME";
			}
			return _xml.ChDescription;
		}
	}

	public string? IsExistChTableSql
	{
		get
		{
			string? result = string.Empty;
			if (string.IsNullOrEmpty(_xml.ChDescription))
			{
				result = "select name from sysobjects where name='T_FIELD_DICTIONARY'";
			}
			return result;
		}
	}

	public SqlGrammar(ConnectionModel? connect)
		: base(connect)
	{
	}

	private string? GetTypeFilter(FieldEntity? field, string sql)
	{
		string? text = field.TypeName;
		int.TryParse(field.Length, out var result);
		string? text2 = field.Length;
		string text3 = field.TypeName.ToUpper();
		if (text3 == "VARCHAR2" || text3 == "VARCHAR")
		{
			text = "VARCHAR";
			text2 = "(" + field.Length + ")";
		}
		if ((text3 == "NUMBER" && result < 9) || text3 == "INT")
		{
			text = "INT";
			text2 = "";
		}
		else if (text3 == "NUMBER" && result >= 9)
		{
			text = "DECIMAL";
			text2 = "(18,2)";
		}
		if (text3 == "TIMESTAMP" || text3 == "DATE")
		{
			text2 = "";
		}
		if (!field.IsNull)
		{
			sql += " Not Null";
		}
		return string.Format(sql, field.TableName, field.Name, text, text2);
	}

	public string? AddColumn(FieldEntity? field)
	{
		string text = "ALTER TABLE [{0}] ADD {1} {2}{3}";
		if (!string.IsNullOrEmpty(field.DefaultValue))
		{
			text = text + " DEFAULT " + field.DefaultValue;
		}
		return GetTypeFilter(field, text);
	}

	public IEnumerable<string?> EditColumn(FieldEntity? field)
	{
		List<string?> list = new List<string?>();
		if (field.IsChange((FieldEntity o) => o.ColumnName))
		{
			list.Add($"EXEC SP_RENAME '{field.TableName}.{field.GetNoChangeValue((FieldEntity o) => o.ColumnName)}','{field.ColumnName}','COLUMN'");
		}
		if (field.IsChange((FieldEntity o) => o.DefaultValue))
		{
			string? sql = $"select b.name from syscolumns a,sysobjects b where a.id=object_id('{field.TableName}') and b.id=a.cdefault and a.name ='{field.Name}' and b.name like 'DF%' ";
			DataSet? dataSet = DBOperatorFactory.GetOperator.ExecSqlToDS(sql, _connect);
			foreach (DataRow row in dataSet.Tables[0].Rows)
			{
				string? item = $"alter table {field.TableName} drop constraint {row[0]} ";
				list.Add(item);
			}
			list.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
			if (!string.IsNullOrEmpty(field.DefaultValue))
			{
				sql = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT DF_{1}_{0} DEFAULT({2}) FOR {1}", field.TableName, field.Name, field.DefaultValue);
				list.Add(sql);
			}
		}
		else
		{
			list.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
		}
		return list;
	}

	public IEnumerable<string?> RemoveColumn(FieldEntity field)
	{
		List<string?> list = new List<string?>();
		if (field.IsForeignKey || field.IsPrimaryKey || !string.IsNullOrEmpty(field.DefaultValue))
		{
			string? sql = $"select b.name from syscolumns a,sysobjects b where a.id=object_id('{field.TableName}') and b.id=a.cdefault and a.name ='{field.Name}' and b.name like 'DF%' ";
			DataSet? dataSet = DBOperatorFactory.GetOperator.ExecSqlToDS(sql, _connect);
			foreach (DataRow row in dataSet.Tables[0].Rows)
			{
				string? item = $"alter table {field.TableName} drop constraint {row[0]} ";
				list.Add(item);
			}
		}
		list.Add($"ALTER TABLE [{field.TableName}] DROP COLUMN {field.TransName}");
		return list;
	}

	public IEnumerable<string?> EditPrimary(TableEntity table)
	{
		List<string?> list = new List<string?>();
		string text = table.PrimaryKeyName;
		string[] array = (from o in table.FileNodes
			where o.IsPrimaryKey
			select o.Name).ToArray();
		if (array.Length > 0)
		{
			if (!string.IsNullOrEmpty(text))
			{
				string? item = $"alter table [{table.Name}] drop constraint {text} ";
				list.Add(item);
			}
			else
			{
				text = "PK" + array[0];
			}
			list.Add($"ALTER TABLE [{table.Name}] ADD CONSTRAINT {text} PRIMARY KEY({string.Join(",", array)})");
		}
		return list;
	}

	public string? SelectTop(TableEntity table, int count)
	{
		string format = "SELECT TOP {1} * FROM {0}";
		return string.Format(format, table.TransName, count);
	}

	public string AppendTabText(string text)
	{
		return TabText(text);
	}

	public string GetErrorInfo(string errorMsg)
	{
		return string.Empty;
	}

	public string? GetProcParamInfo(ProcEntity entity)
	{
		if (entity == null)
		{
			return string.Empty;
		}
		string arg;
		if (entity.ChildNodes.Count > 0)
		{
			List<string> list = new List<string>();
			foreach (ProcParamEntity? childNode in entity.ChildNodes)
			{
				if (childNode.Status == "IN")
				{
					list.Add($"{Environment.NewLine}[{childNode.Name}({childNode.TypeName})]");
				}
				else
				{
					list.Add($"{Environment.NewLine}[{childNode.Name}({childNode.TypeName})-OUT]");
				}
			}
			arg = "(" + string.Join(",", list.ToArray()) + ")";
		}
		else
		{
			arg = "()";
		}
		return $"{"EXEC"} {entity.Name}{arg}";
	}

	public string GetPageingSql(string sql, decimal index, decimal count)
	{
		string format = "SELECT tab.* FROM (SELECT {0} ROWNUMBER,t.* FROM (" + Environment.NewLine + "{1}" + Environment.NewLine + ") t) tab  WHERE tab.ROWNUMBER >= " + index + " and tab.ROWNUMBER < " + count;
		int length = sql.IndexOf("FROM", StringComparison.CurrentCultureIgnoreCase);
		string text = sql.Substring(0, length);
		if (text.IndexOf("count(") > 0 || text.IndexOf("sum(") > 0)
		{
			return sql;
		}
		if (sql.IndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase) > 0)
		{
			sql = sql.Trim();
			if (sql.Substring(0, 6).ToLower() == "select" && !sql.Substring(6).Trim().StartsWith("TOP "))
			{
				sql = sql.Insert(6, " TOP " + count + " ");
			}
		}
		return string.Format(format, "ROW_NUMBER() OVER(ORDER BY GETDATE())", sql);
	}

	protected override string GetRowNumUpdate(string tableName, string where, string column, string? rownumber)
	{
		return string.Format("UPDATE T1 SET {1} FROM ({0}) T1 WHERE T1.ROWNUMBER={2}", string.Format("SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ROWNUMBER,{0}.* FROM {0}", tableName) + " " + where, column, rownumber);
	}

	protected override string GetRowNumDelete(string tableName, string where, string? rownumber)
	{
		return
            $"WITH [T1] AS ({string.Format("SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ROWNUMBER,{0}.* FROM {0}", tableName) + " " + where}) DELETE FROM [T1] WHERE T1.ROWNUMBER={rownumber}";
	}

	public DatabaseType GetConnectionStr(out string connectStr)
	{
		connectStr = "Data Source={3};Initial Catalog={0};User Id={1};Password={2};Trusted_Connection=True;Encrypt=false;";
		connectStr = string.Format(connectStr, _connect.DataBase, _connect.UserID, _connect.PassWord, _connect.Server);
		return DatabaseType.MSSQLServer;
	}
}
